package stocks.server;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class SQLQueries {

	private Connection connection;
	private Statement statement;
	private JdbcConnect dbcon;
	
	public int userLogin(String username, String password)
	{
		dbcon = new JdbcConnect();
		connection = dbcon.getConnection(); //create database connection and query statement
		statement = dbcon.getStatement();
		
		int userid = 0;
		String query =	"SELECT userid from stock_users " +
						"WHERE username='"+ username +"' and " +
						"userpassword='"+ password +"'";
		
		System.out.println(query);
		
		
		try {
			ResultSet rs = statement.executeQuery(query); //execute query
			while(rs.next())
			{
				userid = rs.getInt(1);
				System.out.println("UserID: " + rs.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		dbcon.closeConnection();
		return userid; //if userid = 0, login failed, send notification to client
	}
	
	public int userRegister(String username, String password)
	{
		dbcon = new JdbcConnect(); 
		connection = dbcon.getConnection(); //create database connection and query statement
		statement = dbcon.getStatement();
		
		int userid = 0;
		String query =	"SELECT userid from stock_users " +
						"WHERE username='"+ username +"' and " +
						"userpassword='"+ password +"'";
		
		System.out.println(query);
		
		
		try {
			ResultSet rs = statement.executeQuery(query); //execute query
			while (rs.next())
			{
				userid = rs.getInt(1);
			}
			if(userid != 0){
				dbcon.closeConnection();
				System.out.println("User already exists");
				return 0; //user already exists
			}
			else
			{
				query =	"SELECT userid from stock_users";
				System.out.println(query);
				rs = statement.executeQuery(query);
				while(rs.next())
				{
					userid = rs.getInt(1);
				}
				userid++; //next userid
				query = "INSERT INTO stock_users VALUES(" + userid + ",'" + username +
						"','" + password+"')";
				System.out.println(query);
				statement.executeUpdate(query);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		dbcon.closeConnection();
		return userid; //if userid = 0, login failed, send notification to client
	}
	
	public ArrayList<String> getTransactions(String startdate, String enddate)
	{
		dbcon = new JdbcConnect();
		connection = dbcon.getConnection(); //create database connection and query statement
		statement = dbcon.getStatement();
		ArrayList<String> al = new ArrayList<String>();
		
		ResultSet rs = null;
		String query =	"SELECT transactionid, stockname, userid, quantity, price from completed_orders " +
		"WHERE thedate between to_date('" + startdate + "','dd/mm/yyyy')" + 
		" AND to_date('"+ enddate+"','dd/mm/yyyy')";
		
		//String query =	"SELECT transactionid, stockname, userid, quantity, price from completed_orders where stockname='OSX'";
		System.out.println(query);
		
		try {
			rs = statement.executeQuery(query); //execute query
			int numberOfRows = rs.getRow();
			System.out.println("Number Rows: " + numberOfRows);
			while(rs.next())
			{
				//System.out.println(rs.getString(1));
				al.add(rs.getString(1));
				al.add(rs.getString(2));
				al.add(Integer.toString(rs.getInt(3)));
				al.add(Integer.toString(rs.getInt(4)));
				al.add(Integer.toString(rs.getInt(5)));
				System.out.println(rs.getString(1)+rs.getString(2)+rs.getInt(3)+rs.getInt(4)+rs.getInt(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		dbcon.closeConnection();
		
		return al;
	}
	public ArrayList<String> getStockPrices()
	{
		dbcon = new JdbcConnect();
		connection = dbcon.getConnection(); //create database connection and query statement
		statement = dbcon.getStatement();
		ArrayList<String> al = new ArrayList<String>();
		ResultSet rs = null;
		int userid = 0;
		String query =	"SELECT current_price from stock_items";
		System.out.println(query);
		
		try {
			rs = statement.executeQuery(query);
			while(rs.next())
			{
				al.add(Integer.toString(rs.getInt(1))); //convert int to string
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return al; //return arraylist
	}
	
	public void addCompletedOrder(String transactionID, int stockcode, String stockname, int userid, int qty, int price)
	{
		dbcon = new JdbcConnect();
		connection = dbcon.getConnection(); //create database connection and query statement
		statement = dbcon.getStatement();
		
		String query =	"INSERT INTO completed_orders VALUES('"+transactionID+"',sysdate,"+stockcode+",'"+stockname+"',"+userid+","+qty+","+price+")";
		System.out.println(query);
		try {
			statement.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public int getUserID(String username)
	{
		dbcon = new JdbcConnect();
		connection = dbcon.getConnection(); //create database connection and query statement
		statement = dbcon.getStatement();
		ResultSet rs = null;
		int retval = 0;
		
		String query =	"Select userid from stock_users where username='"+username+"'";
		System.out.println(query);
		try {
			rs = statement.executeQuery(query); //execute query
			while(rs.next())
			{
				retval = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return retval; //return id
	}
	
	public int getStockID(String stockname)
	{
		dbcon = new JdbcConnect();
		connection = dbcon.getConnection(); //create database connection and query statement
		statement = dbcon.getStatement();
		ResultSet rs = null;
		int retval = 0;
		
		String query =	"Select stockcode from stock_items where stockname='"+stockname+"'";
		System.out.println(query);
		try {
			rs = statement.executeQuery(query); //execute query
			while(rs.next())
			{
				retval = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return retval; //return id
	}
}
